﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Transaction.recBillinghandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DomainObject.Administration;
using SIPO.Library.DataAccess.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.Catalog;

using SIPO.Library.Enum;
using SIPO.Web.Support;

using Newtonsoft.Json.Linq;

namespace SIPO.Web.AjaxHandler.Transaction
{

    public class recBillinghandler : SIPO.Web.BaseHandler
    {

        private recBillingRepo recBillingDal = new recBillingRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        recDebtorSectorRepo recDebtorSectorDal = new recDebtorSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private string MethodName = string.Empty;
        private recTransactionTypeRepo recTransactionTypeDal = new recTransactionTypeRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        recDayOffRepo RecDayOffDal = new recDayOffRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            context.Response.Clear();
            context.Response.Buffer = true;
            System.IO.StreamReader sr = new System.IO.StreamReader(context.Request.InputStream);
            string jsonString = string.Empty;

            jsonString = context.Server.UrlDecode(sr.ReadToEnd());

            JObject jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);

            sr.Close();
            sr.Dispose();


            switch (MethodName.ToLower())
            {
                case ("getdatainvoice"):
                    context.Response.Write(GetDataInvoice(context));
                    break;
                case ("getdatainfoinvoice"):
                    context.Response.Write(GetDataInfoInvoice(context));
                    break;
                case ("getdatareport"):
                    context.Response.Write(GetDataReport(context));
                    break;
                case ("getdatavoucherreport"):
                    context.Response.Write(GetDataVoucherReport(context));
                    break;
                case ("getdatapostreport"):
                    context.Response.Write(GetDataPostReport(context));
                    break;
                case ("insert"):
                    context.Response.Write(Insert(context, jObj));
                    break;
                case ("posting"):
                    context.Response.Write(Posting(context, jObj));
                    break;
                case ("update"):
                    context.Response.Write(Update(context, jObj));
                    break;
                default:
                    // do nothing
                    break;
            }

        }

        public string GetDataReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            DateTime? periodeAwal = context.Request["PeriodeAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"], Helper.Culture.Indonesian);
            DateTime? periodeAkhir = context.Request["PeriodeAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            DateTime? dueDateAwal = context.Request["DueDateAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["DueDateAwal"], Helper.Culture.Indonesian);
            DateTime? dueDateAkhir = context.Request["DueDateAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["DueDateAkhir"], Helper.Culture.Indonesian);
            short transTypeID = context.Request["TransTypeID"].Equals(string.Empty) ? (short)0 : short.Parse(context.Request["TransTypeID"].ToString());
            int debtorSectorID = context.Request["DebtorSectorID"].Equals(string.Empty) ? 0 : int.Parse(context.Request["DebtorSectorID"].ToString());
            short balance = context.Request["Balance"].Equals(string.Empty) ? (short)0 : short.Parse(context.Request["Balance"].ToString());

            parameters += "WHERE recUserSector.UserID = ISNULL(" + base.UserID + ", recUserSector.UserID)";

            //konversi tanggal
            string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAwal);
            string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAkhir);
            string sDueDateAwal = dueDateAwal == null ? string.Empty : string.Format("{0:yyyyMMdd}", dueDateAwal);
            string sDueDateAkhir = dueDateAkhir == null ? string.Empty : string.Format("{0:yyyyMMdd}", dueDateAkhir);


            //menngecek data balance
            if (balance == 1)
                parameters += " AND recBilling.Balance = recBilling.Value";

            else if (balance == 2)
                parameters += " AND recBilling.Balance < recBilling.Value";

            if (!sPeriodeAkhir.Equals(string.Empty) && !sPeriodeAwal.Equals(string.Empty))
                parameters += " AND recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)";

            if (!sDueDateAwal.Equals(string.Empty) && !sDueDateAkhir.Equals(string.Empty))
                parameters += " AND recBilling.DueDate BETWEEN ISNULL ('" + sDueDateAwal + "', recBilling.DueDate) AND ISNULL ('" + sDueDateAkhir + "', recBilling.DueDate)";

            //mengecek data transTypeID
            if (transTypeID != (short)0)
                parameters += " AND recBilling.TransTypeID = ISNULL (" + transTypeID + ", recBilling.TransTypeID)";

            //mengecek data debtorSectorID    
            if (debtorSectorID != 0)
                parameters += " AND recBilling.DebtorSectorID = ISNULL (" + debtorSectorID + ", recBilling.DebtorSectorID)";

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);


            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recBillingDal.SelectScalarReport(SQL.Function.Aggregate.Count, "recBilling.DebtorSectorID", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recBillingDal.GetListReport(pageSize, page, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataParamter";
                log.LogMessage = ex.Message;
                log.LogSource = "recRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }



        private string GetDataInvoice(HttpContext context)
        {

            string DebtorSectorID = context.Request.Params["DebtorSectorID"];
            string parameters = Helper.JqGridFilterToSQLParams(context);
            string parameters2 = string.Empty;
            string parameters3 = string.Empty;
            string queryAksi = string.Empty;
            string parametersSubSubSectorIds = string.Empty;

            string queryRegistrasi = @"
select
recBilling.BillingNo
,recBilling.DebtorSectorID
,recBilling.TransTypeID
, recBilling.TariffID
, recBilling.Value
, recBilling.InvoiceNo
, recBilling.InvoiceDetailID
, recBilling.Balance
, recBilling.PrevBillingNo
, recBilling.BillingDate
, recBilling.DueDate
, recBilling.IsCancel
, recBilling.ForPayment
, recBilling.SaikoNo
, recBilling.CreatedDate
, recBilling.CreatedBy
, recBilling.UpdatedDate
, recBilling.UpdatedBy
, recBilling.IsManual
, recTransactionType.TransTypeName
, recSector.SectorName
, recSector.SectorCode
, recSubSector.SubSectorName
, recSubSector.SubSectorCode
, recSubSubSector.SubSubSectorName
, recSubSubSector.SubSubSectorCode
, recSubSubSector.SubSubSectorID
,recRegistration.PreDebtorName AS DebtorName
,recSector.SectorID
,recSubSector.SubSectorID
,recInvoice.PaymentNo
,0 DebtorID
,recUserSector.UserID 
,ISNULL(recBilling.IsHold,0) IsHold
,ISNULL(recBilling.IsRestructur,0) IsRestructur
,recTransactionType.TransTypeCode

FROM transactions.recBilling
inner join transactions.recSectorRegistration
on recbilling.InvoiceNo = recSectorRegistration.InvoiceNo
inner join master.recUserSector on recSectorRegistration.SectorRegistrationID = recUserSector.RegisterSectorID
LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
LEFT JOIN master.recSector ON recSectorRegistration.SectorID = recSector.SectorID
LEFT JOIN master.recSubSector ON recSectorRegistration.SubSectorID = recSubSector.SubSectorID
LEFT JOIN master.recSubSubSector ON recSectorRegistration.SubSubSectorID = recSubSubSector.SubSubSectorID
LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
LEFT JOIN transactions.recRegistration ON recRegistration.RegistrationID =recSectorRegistration.RegistrationID
where recbilling.DebtorSectorID = 0 and recTransactionType.TransTypeCode =1";

            string queryInvoice = @"SELECT
recBilling.BillingNo
, recBilling.DebtorSectorID
---, recBilling.TransTypeID
  ,(CASE WHEN recTransactionType.TransTypeCode=10
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode=11
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode IN(5,6)
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                        ELSE recTransactionType.TransTypeID
                        END) TransTypeID
, recBilling.TariffID
, recBilling.Value
, recBilling.InvoiceNo
, recBilling.InvoiceDetailID
, recBilling.Balance
, recBilling.PrevBillingNo
, recBilling.BillingDate
, recBilling.DueDate
, recBilling.IsCancel
, recBilling.ForPayment
, recBilling.SaikoNo
, recBilling.CreatedDate
, recBilling.CreatedBy
, recBilling.UpdatedDate
, recBilling.UpdatedBy
, recBilling.IsManual
, recTransactionType.TransTypeName
, recSector.SectorName
, recSector.SectorCode
, recSubSector.SubSectorName
, recSubSector.SubSectorCode
, recSubSubSector.SubSubSectorName
, recSubSubSector.SubSubSectorCode
, recSubSubSector.SubSubSectorID
,recDebtor.DebtorName
,recSector.SectorID
,recSubSector.SubSectorID
,recInvoice.PaymentNo
,recDebtor.DebtorID
,0 UserID
,ISNULL(recBilling.IsHold,0) IsHold
,ISNULL(recBilling.IsRestructur,0) IsRestructur
, recTransactionType.TransTypeCode
FROM transactions.recBilling
LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
LEFT JOIN master.recDebtorSector ON recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID
LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
LEFT JOIN master.recDebtor ON recDebtor.DebtorID=recDebtorSector.DebtorID
WHERE recBilling.DebtorSectorID <> 0";
            
                        string queryInvoice2 = @"SELECT
recBilling.BillingNo
, recBilling.DebtorSectorID
---, recBilling.TransTypeID
 , (CASE WHEN recTransactionType.TransTypeCode=10
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode=11
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode IN(5,6)
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                        ELSE recTransactionType.TransTypeID
                        END) TransTypeID
, recBilling.TariffID
, recBilling.Value
, recBilling.InvoiceNo
, recBilling.InvoiceDetailID
, recBilling.Balance
, recBilling.PrevBillingNo
, recBilling.BillingDate
, recBilling.DueDate
, recBilling.IsCancel
, recBilling.ForPayment
, recBilling.SaikoNo
, recBilling.CreatedDate
, recBilling.CreatedBy
, recBilling.UpdatedDate
, recBilling.UpdatedBy
, recBilling.IsManual
, recTransactionType.TransTypeName
, recSector.SectorName
, recSector.SectorCode
, recSubSector.SubSectorName
, recSubSector.SubSectorCode
, recSubSubSector.SubSubSectorName
, recSubSubSector.SubSubSectorCode
, recSubSubSector.SubSubSectorID
,recDebtor.DebtorName
,recSector.SectorID
,recSubSector.SubSectorID
,recInvoice.PaymentNo
,recDebtor.DebtorID
,0 UserID
,ISNULL(recBilling.IsHold,0) IsHold
,ISNULL(recBilling.IsRestructur,0) IsRestructur
, recTransactionType.TransTypeCode
FROM transactions.recBilling
LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
LEFT JOIN master.recDebtorSector ON recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID
LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
LEFT JOIN master.recDebtor ON recDebtor.DebtorID=recDebtorSector.DebtorID
WHERE recBilling.DebtorSectorID <> 0 AND recTransactionType.TransTypeCode <> 2";

            string queryTerminInvoice = string.Empty;


            List<string> listTermin = recBillingDal.GetTerminDueDate();
            DateTime dateCurrent = Convert.ToDateTime(RecDayOffDal.GetCurrentDateFromRecParameter().ToString("dd/MM/yyyy"));

            DateTime dateTerminIsFormula = new DateTime();
            List<string> listTerminIsFormula = recBillingDal.GetTerminDueDateIsFormula();
            foreach (string terminFormula in listTerminIsFormula)
            {
                dateTerminIsFormula = Convert.ToDateTime(terminFormula + "/" + dateCurrent.ToString("yyyy"));
                dateTerminIsFormula = RecDayOffDal.GetDueDate(dateTerminIsFormula.ToString("yyyyMMdd"));
            }
            
            foreach (string terminDate in listTermin)
            {
                //DateTime dateCurrent = Convert.ToDateTime(DateTime.Now.ToString("dd/MM/yyyy"));
                DateTime dateTermin = Convert.ToDateTime(terminDate + "/" + dateCurrent.ToString("yyyy"));

                if (dateCurrent <= dateTermin)
                {
                    //queryInvoice = queryInvoice + " AND recBilling.DueDate <= CONVERT(DATETIME, '" + dateTermin + "', 103)";
                    dateTermin = RecDayOffDal.GetDueDate(dateTermin.ToString("yyyyMMdd"));
                    queryTerminInvoice = " AND (recBilling.DueDate <= CONVERT(DATETIME, '" + dateTermin.ToString("dd/MM/yyyy") + "', 103) OR recBilling.DueDate <= CONVERT(DATETIME, '" + dateTerminIsFormula.ToString("dd/MM/yyyy") + "', 103) )  AND recTransactionType.TransTypeCode = 2";
                    break;
                }
            }


            if (base.DebtorID != null)
            {
                queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                queryInvoice = queryInvoice + " AND recdebtor.DebtorID = " + base.DebtorID;

                queryInvoice2 = queryInvoice2 + " AND recdebtor.DebtorID = " + base.DebtorID;
               
               
            }
            else
            {
                
                if (base.UserTypeID == 1)
                {
                    queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                    queryInvoice = queryInvoice + " AND recdebtor.DebtorID IS NULL ";

                    queryInvoice2 = queryInvoice2 + " AND recdebtor.DebtorID IS NULL ";


                }
                else
                {
                    queryAksi = @"                            
SELECT
recBilling.BillingNo
, recBilling.DebtorSectorID
---, recBilling.TransTypeID
,  (CASE WHEN recTransactionType.TransTypeCode=10
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode=11
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                        WHEN recTransactionType.TransTypeCode IN(5,6)
                        THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                        ELSE recTransactionType.TransTypeID
                        END) TransTypeID

, recBilling.TariffID
, recBilling.Value
, recBilling.InvoiceNo
, recBilling.InvoiceDetailID
, recBilling.Balance
, recBilling.PrevBillingNo
, recBilling.BillingDate
, recBilling.DueDate
, recBilling.IsCancel
, recBilling.ForPayment
, recBilling.SaikoNo
, recBilling.CreatedDate
, recBilling.CreatedBy
, recBilling.UpdatedDate
, recBilling.UpdatedBy
, recBilling.IsManual
, recTransactionType.TransTypeName
, '' SectorName
, 0 SectorCode
, '' SubSectorName
, 0 SubSectorCode
, recCorporateAction.CorporateSector SubSubSectorName
, 0 SubSubSectorCode
, 0 SubSubSectorID
,recCorporateAction.CorporateName DebtorName  
,0 SectorID
,0 SubSectorID
,recInvoice.PaymentNo  
,0 DebtorID 
,0 UserID
,ISNULL(recBilling.IsHold,0) IsHold
,ISNULL(recBilling.IsRestructur,0) IsRestructur
, recTransactionType.TransTypeCode
FROM transactions.recBilling
inner join transactions.recCorporateAction
on recbilling.InvoiceNo = recCorporateAction.InvoiceNo
LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
where recbilling.DebtorSectorID = 0
AND recInvoice.PaymentNo IS NULL
";
                }
            }

            if (base.SubSubSectorIDs != null)
            {
                //sectorID berdasar login base.SubSubSectorIDs
                parametersSubSubSectorIds = parametersSubSubSectorIds == string.Empty ? @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            ) 
                            " : parametersSubSubSectorIds + @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            )";
                //

                //subsectorID berdasar login base.SubSubSectorIDs
                parametersSubSubSectorIds = parametersSubSubSectorIds == string.Empty ? @" WHERE recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )" : parametersSubSubSectorIds + @" AND recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )";
                //

                //subsubsectorID berdasar login base.SubSubSectorIDs

                parametersSubSubSectorIds = parametersSubSubSectorIds == string.Empty ? @" WHERE  recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) " : parametersSubSubSectorIds + @" AND recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) ";

                //


            }

            parametersSubSubSectorIds = parametersSubSubSectorIds == string.Empty ? " WHERE recInvoice.PaymentNo IS NULL" : parametersSubSubSectorIds + " AND recInvoice.PaymentNo IS NULL";

            queryRegistrasi = queryRegistrasi + parametersSubSubSectorIds;
            if (queryTerminInvoice.Trim().Length > 1)
            {
                queryInvoice = queryInvoice + queryTerminInvoice + parametersSubSubSectorIds;
                queryInvoice = queryInvoice + " UNION " + queryInvoice2 + parametersSubSubSectorIds;
            }
            else
            {
                queryInvoice = queryInvoice + parametersSubSubSectorIds;
            }
            
            
            parameters = parameters == string.Empty ? "WHERE recBilling.Value <> 0 AND  recBilling.IsHold = 0  AND recBilling.IsRestructur = 0   " : parameters + " AND recBilling.Value <> 0 AND  recBilling.IsHold = 0 AND recBilling.IsRestructur = 0 ";
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recBilling.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;


            try
            {
                totalCount = Convert.ToInt32(recBillingDal.SelectScalarInvoice(SQL.Function.Aggregate.Count, recBilling.Fields.BillingNo, queryRegistrasi, queryInvoice, queryAksi, parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recBillingDal.GetListInvoice(pageSize, page, sidx, sord, queryRegistrasi, queryAksi, queryInvoice, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recBillinghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }


        private string GetDataInfoInvoice(HttpContext context)
        {

            string InvoiceNo = context.Request.Params["InvoiceNo"];
            string parameters = Helper.JqGridFilterToSQLParams(context);
            string parameters2 = string.Empty;
            string parameters3 = string.Empty;

            if (InvoiceNo != "")
                parameters = parameters == string.Empty ? " WHERE recBilling.InvoiceNo='" + InvoiceNo + "'" : parameters + " AND recBilling.InvoiceNo='" + InvoiceNo + "'";

            //parameters = parameters == string.Empty ? " WHERE recBilling.InvoiceNo IS NULL " : parameters + " AND recBilling.InvoiceNo IS NULL";


            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recBilling.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recBillingDal.SelectScalarInfoInvoice(SQL.Function.Aggregate.Count, recBilling.Fields.BillingNo, parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recBillingDal.GetListInfoInvoice(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataInfoInvoice";
                log.LogMessage = ex.Message;
                log.LogSource = "recBillinghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }

        private string Insert(HttpContext context, JObject jo)
        {
            recTransactionType transType = recTransactionTypeDal.GetById((short)jo["TransTypeID"]);
            JSONHelper helper = new JSONHelper();
            recDebtorSector Dbs = recDebtorSectorDal.GetListByDebtor(Convert.ToInt32(jo["DebtorID"])).Find(x => x.SubSubSectorID == Convert.ToInt32(jo["SubSubSectorID"])); ;

            try
            {
                recBilling objBilling = new recBilling();
                objBilling.TransTypeID = (short)jo["TransTypeID"];
                objBilling.DebtorSectorID = Dbs.DebtorSectorID;
                objBilling.Value = Convert.ToDecimal(jo["Value"].ToString().Replace(".", ""));
                objBilling.Balance = Convert.ToDecimal(jo["Value"].ToString().Replace(".", ""));
                objBilling.BillingNo = jo["TransTypeID"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recBillingDal.GetMaxID(jo["TransTypeID"].ToString()).ToString().PadLeft(5, '0');//Guid.NewGuid().ToString().Substring(0, 9);
                objBilling.BillingDate = DateTime.Now;
                if (transType.TransTypeName.ToLower() == "iuran berkala")
                    objBilling.DueDate = RecDayOffDal.GetDueDateForIuranBerkala();
                else
                    objBilling.DueDate = RecDayOffDal.GetDueDate(DateTime.Now.AddDays(Convert.ToDouble(transType.TermDay)).ToString("yyyyMMdd"));
                objBilling.CreatedDate = DateTime.Now;
                objBilling.CreatedBy = base.UserLoggedIn;
                objBilling.IsManual = true;
                objBilling.ForPayment = (string)jo["Keterangan"];
                recBillingDal.Save(objBilling);
                helper.IsSuccess = true;
                helper.Message = "Data berhasil di simpan !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert Tagihan Manual";
                log.LogMessage = helper.Message;
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }
        private string CompareJenisTagihan(HttpContext context,JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var strId = (string)jo["BillingNo"];
                int transTypeCode = 0;
                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    //int intID = Convert.ToInt32(id);
                    recBilling obj = recBillingDal.GetById(id);
                    if (obj != null)
                    {
                        recTransactionType objTransType = recTransactionTypeDal.GetById(obj.TransTypeID);
                        if (objTransType != null)
                        {
                            if (transTypeCode == objTransType.TransTypeCode)
                            {
                                if (transTypeCode == 10)
                                {
                                    
                                }
                            }
                            
                            transTypeCode = objTransType.TransTypeCode;
                            
                            
                        }
                    }
                     
                    //recActionDal.Delete(intID);
                }
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Compare Jenis Tagihan";
                log.LogMessage = helper.Message;
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);
            
        }
        
        
        private string Update(HttpContext context, JObject jo)
        {
            recTransactionType transType = recTransactionTypeDal.GetById((short)jo["TransTypeID"]);
            JSONHelper helper = new JSONHelper();
            recDebtorSector Dbs = recDebtorSectorDal.GetListByDebtor(Convert.ToInt32(jo["DebtorID"])).Find(x => x.SubSubSectorID == Convert.ToInt32(jo["SubSubSectorID"])); ;

            try
            {
                recBilling objBilling = new recBilling();
                objBilling.TransTypeID = (short)jo["TransTypeID"];
                objBilling.DebtorSectorID = Dbs.DebtorSectorID;
                objBilling.Value = Convert.ToDecimal(jo["Value"].ToString().Replace(".", ""));
                objBilling.Balance = Convert.ToDecimal(jo["Value"].ToString().Replace(".", ""));
                objBilling.BillingNo = jo["BillingNo"].ToString();  //jo["TransTypeID"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recBillingDal.GetMaxID().ToString().PadLeft(5, '0');//Guid.NewGuid().ToString().Substring(0, 9);
                objBilling.BillingDate = Convert.ToDateTime(jo["BillDate"].ToString());
                objBilling.DueDate = Convert.ToDateTime(jo["DueDate"].ToString()); //RecDayOffDal.GetDueDate(DateTime.Now.AddDays(Convert.ToDouble(transType.TermDay)).ToString("yyyyMMdd"));
                objBilling.UpdatedDate = DateTime.Now;
                objBilling.UpdatedBy = base.UserLoggedIn;
                objBilling.IsManual = true;
                objBilling.ForPayment = (string)jo["Keterangan"];
                recBillingDal.Update(objBilling);
                helper.IsSuccess = true;
                helper.Message = "Data berhasil di ubah !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update Tagihan Manual";
                log.LogMessage = helper.Message;
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }
        public string GetDataVoucherReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            DateTime? periodeAwal = context.Request["PeriodeAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"], Helper.Culture.Indonesian);
            DateTime? periodeAkhir = context.Request["PeriodeAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            string jenTrans = context.Request["Jentrans"].Equals(string.Empty) ? string.Empty : context.Request["Jentrans"];


            string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", periodeAwal);
            string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", periodeAkhir);

            if (periodeAwal != null && periodeAkhir != null)
            {
                parameters += "where transactions.recBilling.BillingDate between '" + sPeriodeAwal + "' and '" + sPeriodeAkhir + "'";
                if (jenTrans != string.Empty)
                {
                    parameters += " and master.recTransactionType.transTypeID ='" + jenTrans + "'";
                }
            }
            else if (jenTrans != string.Empty)
            {
                parameters += " where master.recTransactionType.transtypeID ='" + jenTrans + "'";
            }
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);
            string sidx = ((context.Request.QueryString["sidx"]) == null ? recBilling.Fields.BillingDate : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());


            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recBillingDal.SelectScalarReports(SQL.Function.Aggregate.Count, "recBilling.BillingNo", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recBillingDal.GetListReports(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetVoucherReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recBillinghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }
        public string GetDataPostReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            //DateTime? periodeAwal = context.Request["periodeawal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"], Helper.Culture.Indonesian);
            //DateTime? periodeAkhir = context.Request["periodeakhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);

            if (context.Request["param"] != null && context.Request["param"] !="")
            {
                parameters = " where status ='" + context.Request["param"] + "'";
            }
            //string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", periodeAwal);
            //string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", periodeAkhir);

            //if (periodeAwal != null && periodeAkhir != null)
            //{
            //    parameters += "between '" + sPeriodeAwal + "' and '" + sPeriodeAkhir + "'";

            //}

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);
            string sidx = ((context.Request.QueryString["sidx"]) == null ? recBilling.Fields.BillingDate : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());


            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recBillingDal.SelectScalarPostReport(SQL.Function.Aggregate.Count, "recBilling.BillingNo", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recBillingDal.GetListPostReport(totalCount, page, sidx, sord, parameters);
                //if (context.Request["param"] != null&&context.Request["param"] !="")
                //{
                //    totalCount = pagedList.Count;
                //    decimal da = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                //    totalPages = Convert.ToInt32(da);
                //}
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataPostReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recBillinghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }
        private string Posting(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {

                var strId = (string)jo["ID"];
                string GenTrans = recBillingDal.GenerateBillingNoSP("16");

                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    string ID = id;
                    recBillingDal.executeProc(ID, GenTrans);
                }

                string SumPost = recBillingDal.executeProcSumPosting(GenTrans);
                helper.IsSuccess = true;
                helper.Message = ids.Count() + " data berhasil di posting !";
            }
            catch (Exception ex)
            {
                helper.Message = ex.Message;

                helper.IsSuccess = false;

            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Posting & Verifikasi";
                log.LogMessage = helper.Message;
                log.LogSource = "recBillinghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }



            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

    }
}